# Databricks notebook source
# MAGIC %md
# MAGIC # Read original and synthetic data

# COMMAND ----------

df_personas = spark.table("admin_govern_sta_des.tmp_mostlyai.personas")
df_documents_personals = spark.table("admin_govern_sta_des.tmp_mostlyai.documents_personals")
df_persona_adreca = spark.table("admin_govern_sta_des.tmp_mostlyai.`persona-adreca`")
df_adreca = spark.table("admin_govern_sta_des.tmp_mostlyai.adreca")
df_contactes = spark.table("admin_govern_sta_des.tmp_mostlyai.contactes")

# COMMAND ----------

df_synthetic_personas = spark.table("admin_govern_sta_des.tmp_mostlyai.synthetic_personas_31_03")
df_synthetic_documents_personals = spark.table("admin_govern_sta_des.tmp_mostlyai.synthetic_documents_personals_31_03")
df_synthetic_persona_adreca = spark.table("admin_govern_sta_des.tmp_mostlyai.synthetic_persona_adreca_31_03")
df_synthetic_adreca = spark.table("admin_govern_sta_des.tmp_mostlyai.synthetic_adreca_31_03")


# COMMAND ----------

# MAGIC %md
# MAGIC # Count rows

# COMMAND ----------

tables = [
    df_personas,
    df_documents_personals,
    df_persona_adreca,
    df_adreca,
    #df_contactes
]

for df in tables:
    print(f"Table has {df.count()} rows")

# COMMAND ----------

tables = [
    df_synthetic_personas,
    df_synthetic_documents_personals,
    df_synthetic_persona_adreca,
    df_synthetic_adreca
]

for df in tables:
    print(f"Table has {df.count()} rows")

# COMMAND ----------

# MAGIC %md
# MAGIC # Checking cardinalities

# COMMAND ----------

# MAGIC %md
# MAGIC ## Personas

# COMMAND ----------

# MAGIC %md
# MAGIC ### Original

# COMMAND ----------

from pyspark.sql.functions import avg, median

df_grouped = df_personas.groupBy(["ROWID_OBJECT_PERSONA"]).count()
df_avg_repetitions = df_grouped.agg(avg("count").alias("avg_repetitions"), median("count").alias("median_repetitions"))
display(df_avg_repetitions)

# COMMAND ----------

# MAGIC %md
# MAGIC ### Synthetic

# COMMAND ----------

from pyspark.sql.functions import avg, median

df_grouped = df_synthetic_personas.groupBy(["ROWID_OBJECT_PERSONA"]).count()
df_avg_repetitions = df_grouped.agg(avg("count").alias("avg_repetitions"), median("count").alias("median_repetitions"))
display(df_avg_repetitions)

# COMMAND ----------

# MAGIC %md
# MAGIC ## Documents personals

# COMMAND ----------

# MAGIC %md
# MAGIC ### Original

# COMMAND ----------

from pyspark.sql.functions import avg, median

df_grouped = df_documents_personals.groupBy(["ROWID_OBJECT_PERSONA"]).count()
df_avg_repetitions = df_grouped.agg(avg("count").alias("avg_repetitions"), median("count").alias("median_repetitions"))
display(df_avg_repetitions)

# COMMAND ----------

# MAGIC %md
# MAGIC ### Synthetic

# COMMAND ----------

from pyspark.sql.functions import avg, median

df_grouped = df_synthetic_documents_personals.groupBy(["ROWID_OBJECT_PERSONA"]).count()
df_avg_repetitions = df_grouped.agg(avg("count").alias("avg_repetitions"), median("count").alias("median_repetitions"))
display(df_avg_repetitions)

# COMMAND ----------

# MAGIC %md
# MAGIC ## Persona-Adreça

# COMMAND ----------

# MAGIC %md
# MAGIC ### Original

# COMMAND ----------

from pyspark.sql.functions import avg, median

df_grouped = df_persona_adreca.groupBy(["ROWID_OBJECT_PERSONA"]).count()
df_avg_repetitions = df_grouped.agg(avg("count").alias("avg_repetitions"), median("count").alias("median_repetitions"))
display(df_avg_repetitions)

# COMMAND ----------

# MAGIC %md
# MAGIC ### Synthetic

# COMMAND ----------


df_grouped = df_synthetic_persona_adreca.groupBy(["ROWID_OBJECT_PERSONA"]).count()
df_avg_repetitions = df_grouped.agg(avg("count").alias("avg_repetitions"), median("count").alias("median_repetitions"))
display(df_avg_repetitions)

# COMMAND ----------

# MAGIC %md
# MAGIC ## Adreça

# COMMAND ----------

# MAGIC %md
# MAGIC ### Original

# COMMAND ----------

from pyspark.sql.functions import avg, median

df_grouped = df_adreca.groupBy(["ROWID_OBJECT_ADRECA"]).count()
df_avg_repetitions = df_grouped.agg(avg("count").alias("avg_repetitions"), median("count").alias("median_repetitions"))
display(df_avg_repetitions)

# COMMAND ----------

# MAGIC %md
# MAGIC ### Synthetic

# COMMAND ----------

from pyspark.sql.functions import avg, median

df_grouped = df_synthetic_adreca.groupBy(["ROWID_OBJECT_ADRECA"]).count()
df_avg_repetitions = df_grouped.agg(avg("count").alias("avg_repetitions"), median("count").alias("median_repetitions"))
display(df_avg_repetitions)

# COMMAND ----------

# MAGIC %md
# MAGIC # Further analysis

# COMMAND ----------

df_grouped_personas = df_personas.groupBy(["ROWID_OBJECT_PERSONA"]).count().withColumnRenamed("count", "n_repetitions").groupBy("n_repetitions").count().withColumnRenamed("count", "n_pks").orderBy("n_repetitions")
display(df_grouped_personas)

# COMMAND ----------

df_grouped_synthetic_personas = df_synthetic_personas.groupBy(["ROWID_OBJECT_PERSONA"]).count().withColumnRenamed("count", "n_repetitions").groupBy("n_repetitions").count().withColumnRenamed("count", "n_pks").orderBy("n_repetitions")
display(df_grouped_synthetic_personas)

# COMMAND ----------

df_grouped_documents_personals = df_documents_personals.groupBy(["ROWID_OBJECT_PERSONA"]).count().withColumnRenamed("count", "n_repetitions").groupBy("n_repetitions").count().withColumnRenamed("count", "n_fks").orderBy("n_repetitions")
display(df_grouped_documents_personals)

# COMMAND ----------

df_grouped_synthetic_documents_personals = df_synthetic_documents_personals.groupBy(["ROWID_OBJECT_PERSONA"]).count().withColumnRenamed("count", "n_repetitions").groupBy("n_repetitions").count().withColumnRenamed("count", "n_fks").orderBy("n_repetitions")
display(df_grouped_synthetic_documents_personals)

# COMMAND ----------

df_grouped_persona_adreca = df_persona_adreca.groupBy(["ROWID_OBJECT_PERSONA"]).count().withColumnRenamed("count", "n_repetitions").groupBy("n_repetitions").count().withColumnRenamed("count", "n_fks_ROWID_OBJECT_PERSONA").orderBy("n_repetitions")
display(df_grouped_persona_adreca)

# COMMAND ----------

df_grouped_synthetic_persona_adreca = df_synthetic_persona_adreca.groupBy(["ROWID_OBJECT_PERSONA"]).count().withColumnRenamed("count", "n_repetitions").groupBy("n_repetitions").count().withColumnRenamed("count", "n_fks_ROWID_OBJECT_PERSONA").orderBy("n_repetitions")
display(df_grouped_synthetic_persona_adreca)

# COMMAND ----------

df_grouped_persona_adreca = df_persona_adreca.groupBy(["ROWID_OBJECT_ADRECA"]).count().withColumnRenamed("count", "n_repetitions").groupBy("n_repetitions").count().withColumnRenamed("count", "n_fks_ROWID_OBJECT_ADRECA").orderBy("n_repetitions")
display(df_grouped_persona_adreca)

# COMMAND ----------

df_grouped_synthetic_persona_adreca = df_synthetic_persona_adreca.groupBy(["ROWID_OBJECT_ADRECA"]).count().withColumnRenamed("count", "n_repetitions").groupBy("n_repetitions").count().withColumnRenamed("count", "n_fks_ROWID_OBJECT_ADRECA").orderBy("n_repetitions")
display(df_grouped_synthetic_persona_adreca)

# COMMAND ----------

df_grouped_adreca = df_adreca.groupBy(["ROWID_OBJECT_ADRECA"]).count().withColumnRenamed("count", "n_repetitions").groupBy("n_repetitions").count().withColumnRenamed("count", "n_pks").orderBy("n_repetitions")
display(df_grouped_adreca)

# COMMAND ----------

df_grouped_adreca = df_adreca.groupBy(["ROWID_OBJECT_ADRECA"]).count().withColumnRenamed("count", "n_repetitions").groupBy("n_repetitions").count().withColumnRenamed("count", "n_pks").orderBy("n_repetitions")
display(df_grouped_adreca)

# COMMAND ----------

df_grouped_synthetic_adreca = df_synthetic_adreca.groupBy(["ROWID_OBJECT_ADRECA"]).count().withColumnRenamed("count", "n_repetitions").groupBy("n_repetitions").count().withColumnRenamed("count", "n_pks").orderBy("n_repetitions")
display(df_grouped_synthetic_adreca)